<?php
namespace app\admin\service;

use app\model\RatingScaleCloudModel;
use app\model\RatingScaleFactorLevelModel;
use app\model\RatingScaleFactorModel;
use app\model\RatingScaleFactorWarningModel;
use app\model\RatingScaleModel;
use app\model\RatingScaleModeModel;
use app\model\RatingScaleQuestionAnswerModel;
use app\model\RatingScaleQuestionModel;
use app\model\RatingScaleQuestionRelatedFactorModel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Exception;
use think\Service;

class RatingScaleImportService extends Service
{
    public string $message;
    public int $line;
    public int $ratingScaleId;
    private string $ratingScalePreviousName;
    private $defaultMode;
    /**
     * 因子等级数组，赋值名称+等级标准作为key
     * */
    private $factorMark = [];
    /**
     * 因子数组，赋值名称作为key
     * */
    private $factorArr = [];
    /**
     * 题目数组，题号作为key，id作为值
     * */
    private $questionArr = [];
    private $separator = '===';
    private function getDefaultSheetNames($count)
    {
        $return = ['量表题目'];
        if ($count > 1) {
            for ($i = 1; $i < $count; $i++) {
                $return[] = 'Mode' . $i;
            }
        }
        return $return;
    }

    /**直接从表里面获取的量表字段*/
    private function getRatingScalePairs()
    {
        return [
            'B2' => 'name',
        ];
    }

    /**直接从表里面获取的量表信息字段*/
    private function getRatingScaleInfoPairs()
    {
        return [
            'B3' => 'introduce',
            'B4' => 'notice'
        ];
    }

    /**量表题目对应的字段以及一些其他设置*/
    private function getRatingScaleQuetionConfig()
    {
        return [
            'startRow' => 13,
            '[单选]' => 1,
            '[多选]' => 2,
            '[判断]' => 1,
            'num' => 'A',
            'question' => 'B',
            'type' => 'C',
            'answer' => 'D',
            'score' => 'E'
        ];
    }
    private function getFactorLevel()
    {
        return [
            1 => '正常',
            2 => '轻度',
            3 => '中度',
            4 => '中重度',
            5 => '重度',
            6 => '总分异常',
            7 => '除总分异常'
        ];
    }
    private function getFactorLevel2()
    {
        return [
            1 => '正常',
            2 => '极轻度异常',
            3 => '偏轻度异常',
            4 => '轻度异常',
            5 => '中度异常',
            6 => '偏重度异常',
            7 => '重度异常',
            8 => '极重度异常'
        ];
    }
    /**量表因子对应的字段以及一些其他设置*/
    private function getRatingScaleFactorConfig()
    {
        //如果设定里面的level跟这个里面的Level不一致报异常
        $factorLevelArr = $this->getFactorLevel();
        $return = [
            'startRow' => 7,
            'name' => 'B',
            'forMark' => 'D',//用来标记因子的符号
            'factorExplain' => 'F',//因子说明
            'forLevelName' => 5,//用来获取因子程度名称
            'formulaMethod' => 'G',//计算代号，计算方式全部写到工厂里面
            'formula' => 'H',//前端显示给用户看的计算方式string
            'forMarkNumRow' => 6, //excel表格里面sheet页（mode1~modeN）里的第6行
            'markNumSeparator' => '$@',
            'relatedQuestion' => 'I',
            'defaultWarningMethod' => '>=',//默认都是>=
            'level_name'=>'AG',//因子等级名称
            'level' => [
                1 => 'K',
                2 => 'L',
                3 => 'M',
                4 => 'N',
                5 => 'O',
                6 => 'P',
                7 => 'Q',
            ],
            'levelCount' => count($factorLevelArr)
        ];
        return $return;
    }
    private function getRatingScaleFactorConfig2()
    {
        //如果设定里面的level跟这个里面的Level不一致报异常
        $factorLevelArr = $this->getFactorLevel2();
        $return = [
            'startRow' => 7,
            'name' => 'B',
            'forMark' => 'D',//用来标记因子的符号
            'factorExplain' => 'F',//因子说明
            'forLevelName' => 5,//用来获取因子程度名称
            'formulaMethod' => 'G',//计算代号，计算方式全部写到工厂里面
            'formula' => 'H',//前端显示给用户看的计算方式string
            'forMarkNumRow' => 6, //excel表格里面sheet页（mode1~modeN）里的第6行
            'markNumSeparator' => '$@',
            'relatedQuestion' => 'I',
            'defaultWarningMethod' => '>=',//默认都是>=
            'level_name'=>'AH',//因子等级名称
            'level' => [
                1 => 'K',
                2 => 'L',
                3 => 'M',
                4 => 'N',
                5 => 'O',
                6 => 'P',
                7 => 'Q',
                8 => 'R'
            ],
            'levelCount' => count($factorLevelArr)
        ];
        return $return;
    }
    /**量表预警等级字段设定*/
    private function getRatingScaleFactorWarningConfig()
    {
        return [
            'startRow' => 7,
            'importantLevel' => 'V',//2024-7-12改：用标志数量表示严重程度，
            'name' => 'T',
            'sign' => '=',
            'forType' => 'Z',
            'type' => [
//                1 => '因子程度',
//                2 => '因子数量',
//                3 => '预警条件数量',
                1 => '因子程度',
                2 => '阳性项目数',
                3 => '预警条件数量',
            ],
            'forFactorId' => 'AA',
            'forLevel' => 'AD',
            'condition2LevelCount' => 'AB',
            'forMarkId' => 'U',
            'markSeparator' => 'L',//这里不是列数名，是字符串
            'markCount' => 'V',
            'forMarkColorId' => 'W',
            'colorSeparator' => 'C',//这里不是列数名，是字符串
            'isUse' => 'Y'
        ];
    }
    private function getRatingScaleFactorWarningConfig2()
    {
        return [
            'startRow' => 7,
            'importantLevel' => 'W',//2024-7-12改：用标志数量表示严重程度，
            'name' => 'U',
            'sign' => '=',
            'forType' => 'AA',
            'type' => [
//                1 => '因子程度',
//                2 => '因子数量',
//                3 => '预警条件数量',
                1 => '因子程度',
                2 => '阳性项目数',
                3 => '预警条件数量',
            ],
            'forFactorId' => 'AB',
            'forLevel' => 'AE',
            'condition2LevelCount' => 'AC',
            'forMarkId' => 'V',
            'markSeparator' => 'L',//这里不是列数名，是字符串
            'markCount' => 'W',
            'forMarkColorId' => 'X',
            'colorSeparator' => 'C',//这里不是列数名，是字符串
            'isUse' => 'Z'
        ];
    }
    /**量表因子等级字段设定*/
    private function getRatingScaleFactorLevelConfig()
    {
        return [
            'startRow' => 7,
            'overview' => 'AJ',
            'explain' => 'AK',
            'mark' => 'AH',
            'num' => 'AF',
            'level_name' => 'AG',
        ];
    }
    private function getRatingScaleFactorLevelConfig2()
    {
        return [
            'startRow' => 7,
            'overview' => 'AK',
            'explain' => 'AL',
            'mark' => 'AI',
            'num' => 'AG',
            'level_name' => 'AH',
        ];
    }

    /**@return int 1:同时包含===和','，2:只包含===，3：只包含',' */
    private function getFactorRelatedConditionStatus($str)
    {
        if (strpos($str, $this->separator) > -1 && strpos($str, ',') > -1) {
            $return = 1;
        } elseif (strpos($str, $this->separator) > -1) {
            $return = 2;
        } elseif (strpos($str, ',') > -1) {
            $return = 3;
        } else {
            $return = 0;
        }
        return $return;
    }

    /**
     * 保存图片
     * @param $sheet
     * @return array
     */
    public function saveAllImg($sheet): array
    {
        $drawings = $sheet->getDrawingCollection();
        $array = [];
        foreach ($drawings as $drawing) {
            // 提取图片路径
            $imagePath = $drawing->getPath();
            // 读取二进制数据
            $fileContent = file_get_contents($imagePath);
            $ext = '.png';
            $upload = new \app\common\Upload();
            // 设置上传类型和文件大小
            $upload = $upload->defaultConfig();
            $array[] = $upload->upload($fileContent,'binary',$ext);
        }
        return $array;
    }

        /**
     * 因子表
     * @param $sheet
     * @param $modeId
     * @return bool
     */
    private function setRatingScaleFactor($sheet, $modeId)
    {
        $RatingScaleFactor = new RatingScaleFactorModel();
        $RatingScaleQuestionRelatedFactor = new RatingScaleQuestionRelatedFactorModel();
        try {
            $this->factorMark = [];
            $config = $this->getRatingScaleFactorConfig();
            $maxRow = $sheet->getHighestDataRow($config['name']);//获取的最大行数不准
            for ($i = $config['startRow']; $i <= $maxRow; $i++) {
                $insertArr = [];
                $insertArr['mode_id'] = $modeId;
                $insertArr['rating_scale_id'] = $this->ratingScaleId;
                $insertArr['name'] = $sheet->getCell($config['name'] . $i)->getValue();
                $insertArr['formula'] = $sheet->getCell($config['formula'] . $i)->getValue();
                if (empty($insertArr['name'])) {//说明已经没有数据了，直接Break
                    break;
                }
                //插入因子
                $insertArr['formula_method'] = $sheet->getCell($config['formulaMethod'] . $i)->getValue();
                $insertArr['factor_explain'] = $sheet->getCell($config['factorExplain'] . $i)->getValue();
                $warningArr = [];
                foreach ($config['level'] as $value) {
                    $range = $sheet->getCell($value . $i)->getValue();
                    //获取预警的值，这里是找$@1对应的最大值就是开始预警的最小值【例如：$@1对应的值是0===12，是正常，那预警值就是>=13】
                    $temp = explode($config['markNumSeparator'], $sheet->getCell($value . $config['forMarkNumRow'])->getValue());
                    $markNum = (int)$temp[1];
                    if (!empty($range)) {
                        if ($markNum != 1 && empty($warningArr)) {
                            //排除一个正常的范围，货预警范围内的第一个值.
                            $warningArr = explode($this->separator, $range);
                        }
                        if (strpos($range, $this->separator) === false) {//是否是分段数据，不是直接判定为总分关连其他因子的异常
                            $insertArr['is_total'] = 1;
                        }
                    }
                }
                $insertArr['warning'] = floatval($warningArr[0]??0);
                $insertArr['warning_method'] = $config['defaultWarningMethod'];
                $factorId = $RatingScaleFactor->create($insertArr)->id;
                //插入因子相关题目
                $relatedQuestionStr = $sheet->getCell($config['relatedQuestion'] . $i)->getValue();
                switch ($this->getFactorRelatedConditionStatus($relatedQuestionStr)) {
                    case 1://显示为1===80,81,83的形式处理,需要分割两次string
                        $relatedArr = explode(',', $relatedQuestionStr);
                        foreach ($relatedArr as $value) {
                            if ($this->getFactorRelatedConditionStatus($value) == 2) {
                                $tempArr = explode($this->separator, $value);
                                for ($j = (int)$tempArr[0]; $j < (int)$tempArr[1] + 1; $j++) {
                                    $insertArr = [];
                                    $insertArr['rating_scale_id'] = $this->ratingScaleId;
                                    $insertArr['factor_id'] = $factorId;
                                    $insertArr['question_id'] = $this->questionArr[$j];
                                    $RatingScaleQuestionRelatedFactor->create($insertArr);
                                }
                            } else {
                                $insertArr = [];
                                $insertArr['rating_scale_id'] = $this->ratingScaleId;
                                $insertArr['factor_id'] = $factorId;
                                $insertArr['question_id'] = $this->questionArr[(int)$value];
                                $RatingScaleQuestionRelatedFactor->create($insertArr);
                            }
                        }
                        break;
                    case 2://显示为1===100的形式处理
                        $relatedArr = explode($this->separator, $relatedQuestionStr);
                        for ($j = (int)$relatedArr[0]; $j < (int)$relatedArr[1] + 1; $j++) {
                            $insertArr = [];
                            $insertArr['rating_scale_id'] = $this->ratingScaleId;
                            $insertArr['factor_id'] = $factorId;
                            $insertArr['question_id'] = $this->questionArr[$j];
                            $RatingScaleQuestionRelatedFactor->create($insertArr);
                        }
                        break;
                    case 3://显示为1,2,3,4,4,5,6,7的形式处理
                        $relatedArr = explode(',', $relatedQuestionStr);
                        foreach ($relatedArr as $value) {
                            $insertArr = [];
                            $insertArr['rating_scale_id'] = $this->ratingScaleId;
                            $insertArr['factor_id'] = $factorId;
                            $insertArr['question_id'] = $this->questionArr[(int)$value];
                            $RatingScaleQuestionRelatedFactor->create($insertArr);
                        }
                        break;
                    default:
                        throw new Exception('关连题目格式错误');
                }
                //把等级得分标准插入
                if (count($config['level']) != $config['levelCount']) throw new Exception('设定等级错误');
                $mark = $sheet->getCell($config['forMark'] . $i)->getValue();
                $levelName = $sheet->getCell($config['level_name'] . $i)->getValue();
                foreach ($config['level'] as $key => $value) {
                    $range = $sheet->getCell($value . $i)->getValue();
                    if (!empty($range)) {//是否有得分标准数据
                        $temp = explode($config['markNumSeparator'], $sheet->getCell($value . $config['forMarkNumRow'])->getValue());
                        $markNum = $temp[1];
                        $temp = [];
                        if (strpos($range, $this->separator) > -1) {//是否是分段数据，不是直接判定为总分关连其他因子的异常
                            $range = explode($this->separator, $range);
                            $temp['low_range'] = $range[0];
                            $temp['high_range'] = $range[1];
                        }
                        $temp['level'] = $key;
                        $temp['factor_id'] = $factorId;
                        $temp['name'] = $levelName;
                        $temp['mode_id'] = $modeId;
                        $temp['rating_scale_id'] = $this->ratingScaleId;
                        $markKey = $mark . $markNum;
                        $this->factorMark[$markKey] = $temp;
                    }
                }
                //因子数组，插入预警条件使用
                $this->factorArr[$mark] = $factorId;

            }
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }

    private function setRatingScaleFactor2($sheet, $modeId)
    {
        $RatingScaleFactor = new RatingScaleFactorModel();
        $RatingScaleQuestionRelatedFactor = new RatingScaleQuestionRelatedFactorModel();
        try {
            $this->factorMark = [];
            $config = $this->getRatingScaleFactorConfig2();
            $maxRow = $sheet->getHighestDataRow($config['name']);//获取的最大行数不准
            for ($i = $config['startRow']; $i <= $maxRow; $i++) {
                $insertArr = [];
                $insertArr['mode_id'] = $modeId;
                $insertArr['rating_scale_id'] = $this->ratingScaleId;
                $insertArr['name'] = $sheet->getCell($config['name'] . $i)->getValue();
                $insertArr['formula'] = $sheet->getCell($config['formula'] . $i)->getValue();
                if (empty($insertArr['name'])) {//说明已经没有数据了，直接Break
                    break;
                }
                //插入因子
                $insertArr['formula_method'] = $sheet->getCell($config['formulaMethod'] . $i)->getValue();
                $insertArr['factor_explain'] = $sheet->getCell($config['factorExplain'] . $i)->getValue();
                $warningArr = [];
                foreach ($config['level'] as $value) {
                    $range = $sheet->getCell($value . $i)->getValue();
                    //获取预警的值，这里是找$@1对应的最大值就是开始预警的最小值【例如：$@1对应的值是0===12，是正常，那预警值就是>=13】
                    $temp = explode($config['markNumSeparator'], $sheet->getCell($value . $config['forMarkNumRow'])->getValue());
                    $markNum = (int)$temp[1];
                    if (!empty($range)) {
                        if ($markNum != 1 && empty($warningArr)) {
                            //排除一个正常的范围，货预警范围内的第一个值.
                            $warningArr = explode($this->separator, $range);
                        }
                        if (strpos($range, $this->separator) === false) {//是否是分段数据，不是直接判定为总分关连其他因子的异常
                            $insertArr['is_total'] = 1;
                        }
                    }
                }
                $insertArr['warning'] = floatval($warningArr[0]??0);
                $insertArr['warning_method'] = $config['defaultWarningMethod'];
                $factorId = $RatingScaleFactor->create($insertArr)->id;
                //插入因子相关题目
                $relatedQuestionStr = $sheet->getCell($config['relatedQuestion'] . $i)->getValue();
                switch ($this->getFactorRelatedConditionStatus($relatedQuestionStr)) {
                    case 1://显示为1===80,81,83的形式处理,需要分割两次string
                        $relatedArr = explode(',', $relatedQuestionStr);
                        foreach ($relatedArr as $value) {
                            if ($this->getFactorRelatedConditionStatus($value) == 2) {
                                $tempArr = explode($this->separator, $value);
                                for ($j = (int)$tempArr[0]; $j < (int)$tempArr[1] + 1; $j++) {
                                    $insertArr = [];
                                    $insertArr['rating_scale_id'] = $this->ratingScaleId;
                                    $insertArr['factor_id'] = $factorId;
                                    $insertArr['question_id'] = $this->questionArr[$j];
                                    $RatingScaleQuestionRelatedFactor->create($insertArr);
                                }
                            } else {
                                $insertArr = [];
                                $insertArr['rating_scale_id'] = $this->ratingScaleId;
                                $insertArr['factor_id'] = $factorId;
                                $insertArr['question_id'] = $this->questionArr[(int)$value];
                                $RatingScaleQuestionRelatedFactor->create($insertArr);
                            }
                        }
                        break;
                    case 2://显示为1===100的形式处理
                        $relatedArr = explode($this->separator, $relatedQuestionStr);
                        for ($j = (int)$relatedArr[0]; $j < (int)$relatedArr[1] + 1; $j++) {
                            $insertArr = [];
                            $insertArr['rating_scale_id'] = $this->ratingScaleId;
                            $insertArr['factor_id'] = $factorId;
                            $insertArr['question_id'] = $this->questionArr[$j];
                            $RatingScaleQuestionRelatedFactor->create($insertArr);
                        }
                        break;
                    case 3://显示为1,2,3,4,4,5,6,7的形式处理
                        $relatedArr = explode(',', $relatedQuestionStr);
                        foreach ($relatedArr as $value) {
                            $insertArr = [];
                            $insertArr['rating_scale_id'] = $this->ratingScaleId;
                            $insertArr['factor_id'] = $factorId;
                            $insertArr['question_id'] = $this->questionArr[(int)$value];
                            $RatingScaleQuestionRelatedFactor->create($insertArr);
                        }
                        break;
                    default:
                        throw new Exception('关连题目格式错误');
                }
                //把等级得分标准插入
                if (count($config['level']) != $config['levelCount']) throw new Exception('设定等级错误');
                $mark = $sheet->getCell($config['forMark'] . $i)->getValue();
                $levelName = $sheet->getCell($config['level_name'] . $i)->getValue();
                foreach ($config['level'] as $key => $value) {
                    $range = $sheet->getCell($value . $i)->getValue();
                    if (!empty($range)) {//是否有得分标准数据
                        $temp = explode($config['markNumSeparator'], $sheet->getCell($value . $config['forMarkNumRow'])->getValue());
                        $markNum = $temp[1];
                        $temp = [];
                        if (strpos($range, $this->separator) > -1) {//是否是分段数据，不是直接判定为总分关连其他因子的异常
                            $range = explode($this->separator, $range);
                            $temp['low_range'] = $range[0];
                            $temp['high_range'] = $range[1];
                        }
                        $temp['level'] = $key;
                        $temp['factor_id'] = $factorId;
                        $temp['name'] = $levelName;
                        $temp['mode_id'] = $modeId;
                        $temp['rating_scale_id'] = $this->ratingScaleId;
                        $markKey = $mark . $markNum;
                        $this->factorMark[$markKey] = $temp;
                    }
                }
                //因子数组，插入预警条件使用
                $this->factorArr[$mark] = $factorId;

            }
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }


    /**
     * 因子解释表
     * @param $sheet
     * @param $modeId
     * @return bool
     */
    private function setRatingScaleFactorLevel($sheet, $modeId)
    {
        $RatingScaleFactorLevel = new RatingScaleFactorLevelModel();
        try {
            $config = $this->getRatingScaleFactorLevelConfig();
            $maxRow = $sheet->getHighestDataRow($config['mark']);
            for ($i = $config['startRow'], $times = 1; $i < $maxRow + 1; $i++, $times++) {
                //获取标记
                $mark = $sheet->getCell($config['mark'] . $i)->getValue();
                if (empty($mark)) {//没有数据了直接跳出
                    break;
                }
                $insertArr = $this->factorMark[$mark];
                $insertArr['rating_scale_id'] = $this->ratingScaleId;
                $insertArr['mode_id'] = $modeId;
                $insertArr['overview'] = $sheet->getCell($config['overview'] . $i)->getValue();
                $insertArr['explain'] = $sheet->getCell($config['explain'] . $i)->getValue();
                $insertArr['name'] = $sheet->getCell($config['level_name'] . $i)->getValue();
                $RatingScaleFactorLevel->create($insertArr);
            }
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }

    private function setRatingScaleFactorLevel2($sheet, $modeId)
    {
        $RatingScaleFactorLevel = new RatingScaleFactorLevelModel();
        try {
            $config = $this->getRatingScaleFactorLevelConfig2();
            $maxRow = $sheet->getHighestDataRow($config['mark']);
            for ($i = $config['startRow'], $times = 1; $i < $maxRow + 1; $i++, $times++) {
                //获取标记
                $mark = $sheet->getCell($config['mark'] . $i)->getValue();
                if (empty($mark)) {//没有数据了直接跳出
                    break;
                }
                $insertArr = $this->factorMark[$mark];
                $insertArr['rating_scale_id'] = $this->ratingScaleId;
                $insertArr['mode_id'] = $modeId;
                $insertArr['overview'] = $sheet->getCell($config['overview'] . $i)->getValue();
                $insertArr['explain'] = $sheet->getCell($config['explain'] . $i)->getValue();
                $insertArr['name'] = $sheet->getCell($config['level_name'] . $i)->getValue();
                $RatingScaleFactorLevel->create($insertArr);
            }
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }


    /**
     * 因子等级管理表
     * @param $sheet
     * @param $modeId
     * @return bool
     */
    private function setRatingScaleFactorWarning($sheet, $modeId)
    {
        $RatingScaleFactorWarning = new RatingScaleFactorWarningModel();
        try {
            $config = $this->getRatingScaleFactorWarningConfig();
            $maxRow = $sheet->getHighestDataRow($config['name']);
            for ($i = $config['startRow']; $i < $maxRow + 1; $i++) {
                $insertArr = [];
                //能直接从表里面获取的数据
                $insertArr['name'] = $sheet->getCell($config['name'] . $i)->getValue();
                $insertArr['mark_count'] = $sheet->getCell($config['markCount'] . $i)->getValue();
                $insertArr['is_use'] = $sheet->getCell($config['isUse'] . $i)->getValue();
                $insertArr['mode_id'] = $modeId;
                $insertArr['rating_scale_id'] = $this->ratingScaleId;
                $insertArr['sign'] = $config['sign'];
                $insertArr['important_level'] = $sheet->getCell($config['importantLevel'] . $i)->getValue();

                if (empty($insertArr['name'])) {//没数据了直接跳出
                    break;
                }
                //获取type
                $forType = $sheet->getCell($config['forType'] . $i)->getValue();
                foreach ($config['type'] as $key => $value) {
                    if (strpos($forType, $value) > -1) {
                        $insertArr['type'] = $key;
                    }
                }
                if (!key_exists('type', $insertArr)) throw new Exception('表格格式错误');
                //获取markId
                $temp = explode($config['markSeparator'], $sheet->getCell($config['forMarkId'] . $i)->getValue());
                if (count($temp) != 2) throw new Exception('表格格式错误');
                $insertArr['mark_id'] = $temp[1];

                //获取mark颜色id
                $temp = explode($config['colorSeparator'], $sheet->getCell($config['forMarkColorId'] . $i)->getValue());
                if (count($temp) != 2) throw new Exception('表格格式错误');
                $insertArr['mark_color_id'] = $temp[1];
                //条件相关
                $level = 0;
                $forLevel = $sheet->getCell($config['forLevel'] . $i)->getValue();
                switch ((int)$insertArr['type']) {
                    case 1:
                    case 2:
                        $level = substr($sheet->getCell($config['forLevel'] . $i)->getValue(), -1);
                        break;
                    case 3:
                        $forFactorId = $sheet->getCell($config['forFactorId'] . $i)->getValue();
                        if (!key_exists($forFactorId, $this->factorArr)) throw new Exception('赋值不存在');
                        $insertArr['factor_id'] = $this->factorArr[$forFactorId];
                        $insertArr['condition3_level_count'] = (int)$forLevel;
                        break;
                }
                if ($insertArr['type'] == 1) {
                    $forFactorId = $sheet->getCell($config['forFactorId'] . $i)->getValue();
                    if (!key_exists($forFactorId, $this->factorArr)) throw new Exception('赋值不存在');
                    $insertArr['factor_id'] = $this->factorArr[$forFactorId];
                    $insertArr['condition1_level'] = $level;
                }
                if ($insertArr['type'] == 2) {
                    $insertArr['factor_level'] = $level;
                    $insertArr['condition2_level_count'] = $sheet->getCell($config['condition2LevelCount'] . $i)->getValue();
                }
                $RatingScaleFactorWarning->create($insertArr);
            }
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }

    private function setRatingScaleFactorWarning2($sheet, $modeId)
    {
        $RatingScaleFactorWarning = new RatingScaleFactorWarningModel();
        try {
            $config = $this->getRatingScaleFactorWarningConfig2();
            $maxRow = $sheet->getHighestDataRow($config['name']);
            for ($i = $config['startRow']; $i < $maxRow + 1; $i++) {
                $insertArr = [];
                //能直接从表里面获取的数据
                $insertArr['name'] = $sheet->getCell($config['name'] . $i)->getValue();
                $insertArr['mark_count'] = $sheet->getCell($config['markCount'] . $i)->getValue();
                $insertArr['is_use'] = $sheet->getCell($config['isUse'] . $i)->getValue();
                $insertArr['mode_id'] = $modeId;
                $insertArr['rating_scale_id'] = $this->ratingScaleId;
                $insertArr['sign'] = $config['sign'];
                $insertArr['important_level'] = $sheet->getCell($config['importantLevel'] . $i)->getValue();

                if (empty($insertArr['name'])) {//没数据了直接跳出
                    break;
                }
                //获取type
                $forType = $sheet->getCell($config['forType'] . $i)->getValue();
                foreach ($config['type'] as $key => $value) {
                    if (strpos($forType, $value) > -1) {
                        $insertArr['type'] = $key;
                    }
                }
                if (!key_exists('type', $insertArr)) throw new Exception('表格格式错误');
                //获取markId
                $temp = explode($config['markSeparator'], $sheet->getCell($config['forMarkId'] . $i)->getValue());
                if (count($temp) != 2) throw new Exception('表格格式错误');
                $insertArr['mark_id'] = $temp[1];

                //获取mark颜色id
                $temp = explode($config['colorSeparator'], $sheet->getCell($config['forMarkColorId'] . $i)->getValue());
                if (count($temp) != 2) throw new Exception('表格格式错误');
                $insertArr['mark_color_id'] = $temp[1];
                //条件相关
                $level = 0;
                $forLevel = $sheet->getCell($config['forLevel'] . $i)->getValue();
                switch ((int)$insertArr['type']) {
                    case 1:
                    case 2:
                        $level = substr($sheet->getCell($config['forLevel'] . $i)->getValue(), -1);
                        break;
                    case 3:
                        $forFactorId = $sheet->getCell($config['forFactorId'] . $i)->getValue();
                        if (!key_exists($forFactorId, $this->factorArr)) throw new Exception('赋值不存在');
                        $insertArr['factor_id'] = $this->factorArr[$forFactorId];
                        $insertArr['condition3_level_count'] = (int)$forLevel;
                        break;
                }
                if ($insertArr['type'] == 1) {
                    $forFactorId = $sheet->getCell($config['forFactorId'] . $i)->getValue();
                    if (!key_exists($forFactorId, $this->factorArr)) throw new Exception('赋值不存在');
                    $insertArr['factor_id'] = $this->factorArr[$forFactorId];
                    $insertArr['condition1_level'] = $level;
                }
                if ($insertArr['type'] == 2) {
                    $insertArr['factor_level'] = $level;
                    $insertArr['condition2_level_count'] = $sheet->getCell($config['condition2LevelCount'] . $i)->getValue();
                }
                $RatingScaleFactorWarning->create($insertArr);
            }
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }

    /**
     * 量表model表
     * @param $sheet
     * @param $sheetName
     * @return bool
     */
    private function setRatingScaleMode($sheet, $sheetName)
    {
        $RatingScaleMode = new RatingScaleModeModel();
        try {
            //插入mode
            $insertArr = ['name' => $sheetName, 'rating_scale_id' => $this->ratingScaleId];
            if ($sheetName == $this->defaultMode) {
                $insertArr['is_default'] = 1;
            }
            $modeId = $RatingScaleMode->create($insertArr)->id;
            if (isset($insertArr['is_default'])){
                $RatingScaleModel = new RatingScaleModel();
                $RatingScaleModel->where('id', $this->ratingScaleId)->update([
                    'mode_id' => $modeId
                ]);
            }
            if ($this->ratingScalePreviousName == '临床疗效总评量表（CGI）'){
                //插入因子
                if (!$this->setRatingScaleFactor2($sheet, $modeId)) throw new Exception($this->message);
                //插入因子等级
                if (!$this->setRatingScaleFactorLevel2($sheet, $modeId)) throw new Exception($this->message);
                //插入因子预警等级
                if (!$this->setRatingScaleFactorWarning2($sheet, $modeId)) throw new Exception($this->message);
            }else{
                //插入因子
                if (!$this->setRatingScaleFactor($sheet, $modeId)) throw new Exception($this->message);
                //插入因子等级
                if (!$this->setRatingScaleFactorLevel($sheet, $modeId)) throw new Exception($this->message);
                //插入因子预警等级
                if (!$this->setRatingScaleFactorWarning($sheet, $modeId)) throw new Exception($this->message);
            }
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }

    /**
     * 量表题目
     * @param $sheet
     * @return bool
     */
    private function setRatingScaleQuestion($sheet)
    {
        $RatingScaleQuestion = new RatingScaleQuestionModel();
        $RatingScaleQuestionAnswer = new RatingScaleQuestionAnswerModel();
        try {
            $config = $this->getRatingScaleQuetionConfig();
            $maxRow = $sheet->getHighestDataRow($config['num']);
            $thisQuestionNum = 0;
            $questionId = 0;
            for ($i = $config['startRow']; $i < $maxRow + 1; $i++) {
                //获取excel表数据
                $num = $sheet->getCell($config['num'] . $i)->getValue();
                $questionName = $sheet->getCell($config['question'] . $i)->getValue();
                $type = $sheet->getCell($config['type'] . $i)->getValue();
                if (empty($num)) {//说明已经没有数据了，直接Break
                    break;
                }
                if (!key_exists($type, $config)) throw new Exception('题目种类错误');
                $type = $config[$type];
                $answer = $sheet->getCell($config['answer'] . $i)->getValue();
                $score = $sheet->getCell($config['score'] . $i)->getValue();

                //插入题目
                if ($num != $thisQuestionNum) {
                    $thisQuestionNum = $num;
                    $this->questionArr[(int)$num] = $questionId = $RatingScaleQuestion->create([
                        'rating_scale_id' => $this->ratingScaleId,
                        'num' => $num,
                        'name' => $questionName,
                        'type' => $type,
                    ])->id;
                }
                //插入答案
                $RatingScaleQuestionAnswer->create([
                    'rating_scale_id' => $this->ratingScaleId,
                    'num' => $num,
                    'name' => $answer,
                    'score' => $score,
                    'question_id' => $questionId,
                ]);
            }
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }

    /**
     * 量表
     * @param $sheet
     * @param $param
     * @param $channel
     * @return bool
     */
    private function setRatingScaleInfo($sheet, $param,$channel): bool
    {
        $RatingScaleModel = new RatingScaleModel();
        try {
            $ratingScale = [];
            $ratingScalePairs = $this->getRatingScalePairs();
            $ratingScaleInfoPairs = $this->getRatingScaleInfoPairs();
            //渠道【1本地量表，2 云端量表，3区域量表】
            $ratingScale['channel'] = $channel;
            //类型id
            $ratingScale['type_id'] = $param['type_id']??0;
            //公司id
            $ratingScale['company_id'] = $param['company_id']??0;
            //区域id
            $ratingScale['areas_id'] = $param['areas_id']??0;
            //单位id
            $ratingScale['unit_id'] = $param['unit_id']??0;
            //测评指导价
            $ratingScale['use_price'] = $param['use_price']??0;
            //申请解读费用
            $ratingScale['interpret_price'] = $param['interpret_price']??0;
            //封面图
            if (!empty($param['cover'])) {
                $ratingScale['cover'] = $param['cover'];
            }else{
                $imgs = $this->saveAllImg($sheet);
                $ratingScale['cover'] = $imgs[0]??env('SETTING.RATING_SCALE_COVER');
            }
            //是否团体
//            $ratingScale['is_group'] = $param['is_group'];
            //添加时间
            $ratingScale['time'] = time();
            //插入默认的Mode
            $this->defaultMode = $sheet->getCell('B8')->getValue();
            //插入量表
            foreach ($ratingScalePairs as $key => $value) {
                $ratingScale[$value] = $sheet->getCell($key)->getValue();
            }
            $ratingScale['previous_name'] = $this->ratingScalePreviousName = $ratingScale['name'];
            if(!empty($param['name'])){
                //用户可以使用自己定义的名称
                $ratingScale['name'] = $param['name'];
            }
//            $ratingScale['name'] = $this->replaceName($ratingScale['name']);
            if(!isset($ratingScale['name'])){
                throw new Exception('缺少量表显示名称');
            }else{
                if ($channel == 3){
                    //同一个平台下不能重复同名的量表
                    $oldName = $RatingScaleModel->where([
                        ['company_id','=',$param['company_id']],
                        ['channel','=',3],
                    ])->column('name');
                }else{
                    //同一个单位下不能重复同名的量表
                    $oldName = $RatingScaleModel->where('unit_id','=',$param['unit_id'])->column('name');
                }
                if(in_array($ratingScale['name'],$oldName)) throw new Exception('“'.$ratingScale['name'].'”量表已存在！');
            }
            $ratingScale['uid'] = $param['uid'];
            //插入量表信息
            foreach ($ratingScaleInfoPairs as $key => $value) {
                $ratingScale[$value] = $sheet->getCell($key)->getValue();
            }
            $ratingScale['use_scene'] = '1,2,3,4,5,6,7,8';
            $this->ratingScaleId = $RatingScaleModel->create($ratingScale)->id;
            if ($channel == 2){
                //云端量表
                $RatingScaleCloud = new RatingScaleCloudModel();
                $RatingScaleCloud->insert([
                    'uid' => $param['uid'],
                    'rating_scale_id' => $this->ratingScaleId,
                    'name' => $param['name'],
                    'download_price' => $param['download_price']??0,
                    'time' => time()
                ]);
            }
            //插入题目信息
            if (!$this->setRatingScaleQuestion($sheet)) throw new Exception($this->message);
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        }
    }

    /**
     * 导入量表
     * @param string $path 文件地址
     * @param array $param 参数
     * @param int $channel 渠道【1本地量表，2 云端量表，3区域量表】
     * @return false|string
     */
    public function import(string $path, array $param, int $channel=1): bool|string
    {
        $fhandle = null;
        $spreadSheet = null;
        try {
            $fhandle = fopen($path, 'rb'); // 以二进制只读模式打开
            $spreadSheet = IOFactory::load($path);
            $sheetNames = $spreadSheet->getSheetNames();
            $defaultNames = $this->getDefaultSheetNames(count($sheetNames));
            foreach ($sheetNames as $key => $value) {
                if (in_array($value, $defaultNames)) {
                    $sheet = $spreadSheet->getSheet($key);
                    //第一页添加量表信息和量表题目信息
                    if ($key == 0) {
                        if(!in_array($value, $defaultNames)) throw new Exception('量表格式错误，Sheet名称不对，正确的为:量表题目！');
                        if (!$this->setRatingScaleInfo($sheet, $param,$channel)) throw new Exception($this->message.'line:'.$this->line);
                    } else {
                        //第二页开始添加量表mode信息
                        if (!$this->setRatingScaleMode($sheet, $value)) throw new Exception($this->message.'line:'.$this->line);
                    }
                }
            }
            $this->message = '新增成功！';
            return true;
        } catch (\Exception $e) {
            $this->message = $e->getMessage();
            $this->line = $e->getLine();
            return false;
        } finally {
            // 1. 关闭文件句柄
            if (is_resource($fhandle)) {
                fclose($fhandle);
            }
            // 2. 销毁Spreadsheet对象
            if ($spreadSheet instanceof Spreadsheet) {
                $spreadSheet->disconnectWorksheets();
                unset($spreadSheet);
            }
            gc_collect_cycles();
        }
    }

    public function replaceName($modifiedB2)
    {
        // 同义词分组：每组内的词汇可互相替换
        $synonymGroups = [
            ['量表', '问卷', '测评', '测验', '测试'],
            ['自评', '自我评定', '状态自评'],
            ['成熟度', '成熟程度'],
            ['能力', '能力水平'],
            ['焦虑', '焦虑状态'],
            ['抑郁', '抑郁状态'],
            ['诊断', '评定'],
        ];

        // 遍历每组同义词，寻找可替换的词汇
        foreach ($synonymGroups as $group) {
            // 查找字符串中包含的组内词汇
            $matchedWord = null;
            foreach ($group as $word) {
                if (str_contains($modifiedB2, $word)) {
                    $matchedWord = $word;
                    break;
                }
            }

            // 如果找到匹配词，随机替换为组内其他词
            if ($matchedWord) {
                // 排除匹配词，获取候选替换词列表
                $candidates = array_diff($group, [$matchedWord]);
                // 随机选一个替换词
                $replaceWord = $candidates[array_rand($candidates)];
                // 执行替换
                $modifiedB2 = str_replace($matchedWord, $replaceWord, $modifiedB2);
                break; // 只替换一个词，避免过度修改
            }
        }

        return $modifiedB2;
    }
}